
CREATE PROCEDURE sprocCXCRMAgentCallStatusReportSummary 1, '', ''
(
	@ReportType		smallint   
	,@ReportDate1		varchar(50)   
	,@ReportDate2		varchar(50) 
)
AS
Declare @MainCallStatusTypeID int
Declare @CallStatusTypeID int
Declare @StatusCode int
Declare @MainEN nvarchar(255)
Declare @SubEN nvarchar(255)
Declare @strHTML nvarchar(Max)
Declare @strBlankTD nvarchar(1000)
Declare @strBlankGreenTD nvarchar(2000)
Declare @strBlankBlueTD nvarchar(2000)

Declare @StartDate datetime
Declare @EndDate datetime

SET @StartDate = GETDATE() - 13
SET @EndDate = GETDATE()

SET @strHTML = '<html><body><table align=''center'' border=''1'' cellpadding=''2'' cellspacing=''2''><tr><th align=''left'' width=''350''>CALL STATUS BREAKDOWN</th>'
SET @strBlankTD = ''
SET @strBlankGreenTD = ''
SET @strBlankBlueTD = ''

Declare @NoOfDay int
Declare @DayCount int
Select @NoOfDay = DATEDIFF(DD, @StartDate, @EndDate) + 1
SET @DayCount = @NoOfDay

Declare @countDay int

--First Row
SET @countDay = 0
Select CAST(@NoOfDay as varchar)
WHILE @NoOfDay > 0
BEGIN
	
	SET @NoOfDay = @NoOfDay - 1
	SET @strHTML = @strHTML + '<th align=''center'' width=''80''>' + (SELECT CONVERT(CHAR(2),(CONVERT(VARCHAR,DATEADD(dd,@countDay,@StartDate),103))) + ' - ' + (SELECT LEFT(DATENAME(mm,DATEADD(dd,@countDay,@StartDate)),3))) + '</th>'
	SET @strBlankTD = @strBlankTD + '<td>&nbsp;</td>'
	SET @strBlankGreenTD = @strBlankGreenTD + '<td bgcolor=''green''>&nbsp;</td>'
	SET @strBlankBlueTD = @strBlankBlueTD + '<td bgcolor=''#99ccff''>&nbsp;</td>'
	--Select GETDATE()-@NoOfDay
	SET @countDay = @countDay + 1
END
		
SET @strHTML = @strHTML + '<th align=''center'' width=''100''>Total</th></tr>'
SET @strBlankTD = @strBlankTD + '<td>&nbsp;</td>'
SET @strBlankGreenTD = @strBlankGreenTD + '<td bgcolor=''green''>&nbsp;</td>'
SET @strBlankBlueTD = @strBlankBlueTD + '<td bgcolor=''#99ccff''>&nbsp;</td>'

--Second Row
SET @strHTML = @strHTML + '<tr><td>&nbsp;</td>'
SET @NoOfDay = @DayCount
SET @countDay = 0
WHILE @NoOfDay > 0
BEGIN
	
	SET @NoOfDay = @NoOfDay - 1
	SET @strHTML = @strHTML + '<td align=''center''>' + (SELECT LEFT(DATENAME(DW,DATEADD(dd,@countDay,@StartDate)),3)) + '</td>'
	--Select GETDATE()-@NoOfDay
	SET @countDay = @countDay + 1
END
		
SET @strHTML = @strHTML + '<td>&nbsp;</td></tr>'
--Select @strHTML
--Select @NoOfDay NoofDay
--Select @DayCount DayCount
--Select @strBlankTD BlankTD
--Ineffetive Presentation
SET @countDay = 0
SET @NoOfDay = @DayCount
--SET @strHTML = @strHTML + '<tr><td align=''left'' bgcolor=''green'' style=''color:white;''>Ineffetive Presentation:</td>' + @strBlankGreenTD+ '</tr>'
		


--Select @strHTML

--BODY
SET @NoOfDay = @DayCount
Declare curMainStatus cursor for
	Select MainCallStatusTypeID, EN from tblCXCRMMainCallStatusType Where Status = 1 ORDER BY MainCallStatusTypeID
	
	Open curMainStatus 
		fetch next from curMainStatus into @MainCallStatusTypeID, @MainEN
			
		
			WHILE (@@fetch_status <> -1)
			BEGIN
				
				SET @strHTML = @strHTML + '<tr><td align=''left'' bgcolor=''#99ccff''><b style=''font-size:20;''>' + @MainEN + '</b></td>' + @strBlankBlueTD + '</tr>'
				Declare curSubStatus cursor for
				Select CallStatusTypeID, StatusCode, EN from tblCXCRMCallStatusType Where MainCallStatusTypeID = @MainCallStatusTypeID And Status = 1
				
				Open curSubStatus 
				fetch next from curSubStatus into @CallStatusTypeID, @StatusCode, @SubEN
					
					WHILE (@@fetch_status <> -1)
					BEGIN
							Declare @noOfSubStatusTotalCount int
							SET @noOfSubStatusTotalCount = 0
							SET @strHTML = @strHTML + '<tr><td align=''left''>' + @SubEN + '</td>'
							SET @NoOfDay = @DayCount
							WHILE @NoOfDay > 0
							BEGIN
								--Select @MainCallStatusTypeID, @MainEN, @StatusCode, @SubEN
								--Select @StartDate
								Declare @subStatusCount int
								Select @subStatusCount = COUNT(CXCRMID) from tblCXCRMCallStatusLog Where CallStatusTypeID = @CallStatusTypeID And MainCallStatusTypeID = @MainCallStatusTypeID And CONVERT(varchar(8), CreatedDate, 112) = Convert(varchar(8), @StartDate, 112)
								--Select @subStatusCount 'SUBSTATUSCOUNT'
								
								IF @subStatusCount IS NULL BEGIN SET @subStatusCount = 0 END
								
								SET @noOfSubStatusTotalCount = @noOfSubStatusTotalCount + @subStatusCount
								SET @strHTML = @strHTML + '<td align=''center''>' + CAST( @subStatusCount as varchar)  + '</td>'
								SET @NoOfDay = @NoOfDay - 1
								SET @StartDate = @StartDate + 1
								
								--Select GETDATE()-@NoOfDay
							END
							SET @strHTML = @strHTML + '<td align=''center''><b>' +  CAST( @noOfSubStatusTotalCount as varchar)  + '</b></td></tr>'
							SET @StartDate = @StartDate - @DayCount
					fetch next from curSubStatus into @CallStatusTypeID, @StatusCode, @SubEN
					END
					
					
					
				Close curSubStatus
				Deallocate curSubStatus
				
				SET @strHTML = @strHTML + '<tr><td align=''left'' bgcolor=''silver''><b> Total ' + @MainEN + '</b></td>'
				Declare @noOfMainStatusTotalCount int
				SET @noOfMainStatusTotalCount = 0
				SET @NoOfDay = @DayCount
				--MAIN STATUS TOTAL
				WHILE @NoOfDay > 0
				BEGIN
					Declare @noOfMainStatusCount int
					
					Select @noOfMainStatusCount = COUNT(CXCRMID) from tblCXCRMCallStatusLog Where MainCallStatusTypeID = @MainCallStatusTypeID And CONVERT(varchar(8), CreatedDate, 112) = Convert(varchar(8), @StartDate, 112)
					
					SET @strHTML = @strHTML + '<td align=''center'' bgcolor=''silver''><b>' + CAST( @noOfMainStatusCount as varchar)  + '</b></td>'
					SET @NoOfDay = @NoOfDay - 1
					SET @StartDate = @StartDate + 1
					SET @noOfMainStatusTotalCount = @noOfMainStatusTotalCount + @noOfMainStatusCount
				END
				SET @strHTML = @strHTML + '<td align=''center'' bgcolor=''silver''><b>' +  CAST( @noOfMainStatusTotalCount as varchar)  + '</b></td></tr>'
				SET @StartDate = @StartDate - @DayCount
					
				--END MAIN STATUS TOTAL
			fetch next from curMainStatus into @MainCallStatusTypeID, @MainEN
			END
			
			
Close curMainStatus
Deallocate curMainStatus

SET @strHTML = @strHTML + '</table></body></html>'
PRINT @strHTML
select @strHTML